﻿Imports System.Data.SQLite

Public Class C_SQL
#Region "初始化"


    Implements IDisposable

    Public cn As SQLiteConnection
    Public Sub New(DBPath As String)
        cn = New SQLiteConnection(DBPath)
    End Sub
#Region "IDisposable Support"
    Private disposedValue As Boolean ' 要检测冗余调用

    ' IDisposable
    Protected Overridable Sub Dispose(disposing As Boolean)
        If Not disposedValue Then
            If disposing Then
                ' TODO: 释放托管状态(托管对象)。
                Try
                    cn.Dispose()
                Catch ex As Exception

                End Try
            End If

            ' TODO: 释放未托管资源(未托管对象)并在以下内容中替代 Finalize()。
            ' TODO: 将大型字段设置为 null。
        End If
        disposedValue = True
    End Sub

    ' TODO: 仅当以上 Dispose(disposing As Boolean)拥有用于释放未托管资源的代码时才替代 Finalize()。
    'Protected Overrides Sub Finalize()
    '    ' 请勿更改此代码。将清理代码放入以上 Dispose(disposing As Boolean)中。
    '    Dispose(False)
    '    MyBase.Finalize()
    'End Sub

    ' Visual Basic 添加此代码以正确实现可释放模式。
    Public Sub Dispose() Implements IDisposable.Dispose
        ' 请勿更改此代码。将清理代码放入以上 Dispose(disposing As Boolean)中。
        Dispose(True)
        ' TODO: 如果在以上内容中替代了 Finalize()，则取消注释以下行。
        ' GC.SuppressFinalize(Me)
    End Sub
#End Region
#End Region
#Region "插入"
    Function Insert(ByVal SuperSQL As String, Optional TimeOut As Integer = 30) As Object
        Try
            Using UPData_command As New SQLiteCommand
                Dim BackID As String = ""
                UPData_command.CommandTimeout = TimeOut
                UPData_command.Connection = cn
                UPData_command.CommandType = CommandType.Text
                UPData_command.CommandText = SuperSQL & " select @@IDENTITY"
                If cn.State = ConnectionState.Closed Then cn.Open()
                Return UPData_command.ExecuteScalar()
            End Using
        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
        End Try
    End Function
    Public Function Insert(ByVal TBName_THis As String, ByVal Columns() As String, ByVal Values() As Object, Optional TimeOut As Integer = 30) As Object
        Return Insert_Tran(TBName_THis, Columns, Values, TimeOut, Nothing, True)
    End Function
    Public Function Insert_Tran(ByVal TBName_THis As String, ByVal Columns As List(Of String), ByVal Values As List(Of Object), Optional TimeOut As Integer = 30, Optional tranction As SQLiteTransaction = Nothing, Optional NeedIDENTITY As Boolean = True) As Object
        Return Insert_Tran(TBName_THis, Columns.ToArray, Values.ToArray, TimeOut, tranction, NeedIDENTITY)
    End Function
    Public Function Insert_Tran(ByVal TBName_THis As String,
                           ByVal Columns() As String,
                           ByVal Values() As Object,
                           Optional TimeOut As Integer = 30,
                           Optional tranction As SQLiteTransaction = Nothing,
                           Optional NeedIDENTITY As Boolean = True) As Object
        Dim SqlStr As String = "未成功创建SQl语句"
        Try
            Using UPData_command As New SQLiteCommand
                UPData_command.Transaction = tranction
                UPData_command.Connection = cn
                UPData_command.CommandTimeout = TimeOut
                UPData_command.CommandType = CommandType.Text
                Dim Columnstring As New List(Of String)
                Dim Valuestring As New List(Of String)
                For i As Integer = 0 To Columns.Count - 1
                    Columnstring.Add("[" & Columns(i) & "]")
                    Valuestring.Add("@a" & i)
                    UPData_command.Parameters.AddWithValue("@a" & i, Values(i))
                Next
                SqlStr = "insert into " & TBName_THis & "(" & String.Join(",", Columnstring) & ") values (" & String.Join(",", Valuestring) & ")" & If(NeedIDENTITY, "select @@IDENTITY", "")
                UPData_command.CommandText = SqlStr
                If cn.State = ConnectionState.Closed Then cn.Open()
                Return UPData_command.ExecuteScalar
            End Using
        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
        End Try
    End Function


#End Region
#Region "GetDataTable "
    Function GetDataTable(ByVal SuperSQL As String, Optional TimeOut As Integer = 300) As System.Data.DataTable
        Try
            Using UPData_command As New SQLiteCommand
                UPData_command.CommandType = CommandType.Text
                UPData_command.CommandTimeout = TimeOut
                UPData_command.Connection = cn
                UPData_command.CommandText = SuperSQL
                Using dataAdapter As SQLite.SQLiteDataAdapter = New SQLite.SQLiteDataAdapter(UPData_command)
                    Dim dataSet As System.Data.DataSet = New System.Data.DataSet
                    If cn.State = ConnectionState.Closed Then cn.Open()
                    dataAdapter.Fill(dataSet)
                    Return dataSet.Tables(0)
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
        End Try
    End Function
    Function GetDataSet(ByVal SuperSQL As String, Optional TimeOut As Integer = 300) As System.Data.DataSet
        Try
            Using UPData_command As New SQLiteCommand
                UPData_command.CommandType = CommandType.Text
                UPData_command.CommandTimeout = TimeOut
                UPData_command.Connection = cn
                UPData_command.CommandText = SuperSQL
                Using dataAdapter As SQLite.SQLiteDataAdapter = New SQLite.SQLiteDataAdapter(UPData_command)
                    Dim dataSet As System.Data.DataSet = New System.Data.DataSet
                    If cn.State = ConnectionState.Closed Then cn.Open()
                    dataAdapter.Fill(dataSet)
                    Return dataSet
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
        End Try
    End Function
#End Region
#Region "返回第一行第一列数据"
    Public Function GetObject(ByVal SuperSQL As String, Optional TimeOut As Integer = 30) As Object
        Try
            Using sqlCommand As SQLiteCommand = New SQLiteCommand(SuperSQL, cn)
                sqlCommand.CommandTimeout = TimeOut
                If cn.State = ConnectionState.Closed Then cn.Open()
                Return sqlCommand.ExecuteScalar
            End Using
        Catch ex As Exception
            Throw New NotSupportedException(ex.Message)
        Finally
            cn.Close()
        End Try
    End Function
#End Region
#Region "直接返回影响值"
    Function effect(ByVal SuperSQL As String, Optional TimeOut As Integer = 30) As Integer
        Try
            Using sqlCommand As SQLiteCommand = New SQLiteCommand(SuperSQL, cn)
                sqlCommand.CommandTimeout = TimeOut
                If cn.State = ConnectionState.Closed Then cn.Open()
                Return sqlCommand.ExecuteNonQuery
            End Using
        Catch ex As Exception
            Throw New NotSupportedException(ex.Message)
        Finally
            cn.Close()
        End Try
    End Function
#End Region
#Region "批量导入数据库"
    Function BctchAdd_To_DateBase(合并提交SQLstr As String, DT_This As DataTable, TBName_This As String, Optional TimeOut As Integer = 300) As Integer
        If 合并提交SQLstr = "" Then
            Return BctchAdd_To_DateBase_new(DT_This, TBName_This, TimeOut)
        Else


            Using sct As New SQLServer_CMD_Tran({New SQLServer_CMD_Tran_item("", 合并提交SQLstr),
                                                 New SQLServer_CMD_Tran_item(DT_This, TBName_This)}, Me)
                sct.ExecuteNonQuery()
                If sct.Success = True Then
                    sct.Commit()
                    Return sct.EffectCount
                Else
                    Throw New Exception(sct.ErrStr)
                End If
            End Using
        End If
    End Function
    Function BctchAdd_To_DateBase_new(DT_This As DataTable,
                                      TBName_This As String,
                                      Optional TimeOut As Integer = 300,
                                      Optional tranction As SQLiteTransaction = Nothing) As Integer
        Try
            Dim DT_Aim = GetDataTable(DCM.SQLStr_Select(TBName_This, "", 0,,, DCM.SQL_StrDll.Enum_DataBaseType.SQlite), TimeOut)
            Dim DT_Add = DCM.SQL_StrDll.批量导入验证并制作批量导入表格(DT_This, DT_Aim)
            '开始导入
            Dim Colnames As New List(Of String)
            For i As Integer = 0 To DT_Add.Columns.Count - 1
                Colnames.Add(DT_Add.Columns(i).ColumnName)
            Next
            If IsNothing(tranction) Then
                Using tranction1 As SQLiteTransaction = cn.BeginTransaction()
                    For Each dr As DataRow In DT_Add.Rows
                        Dim Values As New List(Of Object)
                        For i As Integer = 0 To DT_Add.Columns.Count - 1
                            Values.Add(dr(i))
                        Next
                        Insert_Tran(TBName_This, Colnames, Values, TimeOut, tranction1, False)
                    Next
                    tranction1.Commit()
                End Using
            Else
                For Each dr As DataRow In DT_Add.Rows
                    Dim Values As New List(Of Object)
                    For i As Integer = 0 To DT_Add.Columns.Count - 1
                        Values.Add(dr(i))
                    Next
                    Insert_Tran(TBName_This, Colnames, Values, TimeOut, tranction, False)
                Next
            End If
            Return DT_Add.Rows.Count
        Catch ex As Exception
            Throw New Exception("批量导入数据发生错误:" & ex.Message)
        End Try
    End Function
#Region "事务_新"
    Public Class SQLServer_CMD_Tran
        Implements IDisposable
        Public CSQL As C_SQL
        Dim CMD As SQLite.SQLiteCommand
        Public Success As Boolean
        Public ErrStr As String
        Public Items() As SQLServer_CMD_Tran_item
        Dim ISExecuteNonQuery As Boolean = False
        Public Function EffectCount() As Integer
            If IsNothing(Items) Then Throw New Exception("未正常初始化执行语句列表!")
            Dim rcount As Integer = 0
            For Each item In Items
                rcount += item.EffectCount
            Next
            Return rcount
        End Function
        Public Sub ExecuteNonQuery()
            Try
                If ISExecuteNonQuery Then Return
                ISExecuteNonQuery = True
                If IsNothing(CMD) = False Then CMD.Dispose()
                CMD = New SQLiteCommand("", CSQL.cn)
                If IsNothing(CSQL.cn) Then Throw New Exception("未初始化数据库连接")
                If CSQL.cn.State = ConnectionState.Closed Then CSQL.cn.Open()
                CMD.Transaction = CSQL.cn.BeginTransaction
                If IsNothing(Items) Then Throw New Exception("未正常初始化执行语句列表!")
                For i As Integer = 0 To Items.Count - 1
                    Select Case Items(i).方式
                        Case SQLServer_CMD_Tran_item.Enum_方式.DataTable
                            Items(i).EffectCount = CSQL.BctchAdd_To_DateBase_new(Items(i).data, Items(i).TBName, 300, CMD.Transaction)
                        Case SQLServer_CMD_Tran_item.Enum_方式.Text
                            CMD.CommandText = Items(i).SQLStr
                            Try
                                Items(i).EffectCount = CMD.ExecuteNonQuery()
                            Catch ex As Exception
                                Throw New Exception(Items(i).TBName & "执行失败:" & ex.Message)
                            End Try
                    End Select
                Next
                Success = True
            Catch ex As Exception
                ErrStr = ex.Message
                Success = False
            End Try
        End Sub
        Public Sub Commit()
            If IsNothing(CMD) Then Throw New Exception("未执行sql语句")
            CMD.Transaction.Commit()
            ISExecuteNonQuery = False
        End Sub
        Public Sub RollBack()
            If IsNothing(CMD) Then Throw New Exception("未执行sql语句")
            CMD.Transaction.Rollback()
            ISExecuteNonQuery = False
        End Sub
        Public Sub New(Items() As SQLServer_CMD_Tran_item, CQSL As C_SQL)
            Me.Items = Items
            Me.CSQL = CSQL
        End Sub

#Region "IDisposable Support"
        Private disposedValue As Boolean ' 检测冗余的调用

        ' IDisposable
        Protected Overridable Sub Dispose(disposing As Boolean)
            If Not Me.disposedValue Then
                If disposing Then
                    ' TODO:  释放托管状态(托管对象)。
                    If IsNothing(CMD) = False Then
                        If IsNothing(CMD.Transaction) = False Then CMD.Transaction.Dispose()
                        CMD.Dispose()
                    End If
                End If

                ' TODO:  释放非托管资源(非托管对象)并重写下面的 Finalize()。
                ' TODO:  将大型字段设置为 null。
            End If
            Me.disposedValue = True
        End Sub

        ' TODO:  仅当上面的 Dispose(ByVal disposing As Boolean)具有释放非托管资源的代码时重写 Finalize()。
        'Protected Overrides Sub Finalize()
        '    ' 不要更改此代码。    请将清理代码放入上面的 Dispose(ByVal disposing As Boolean)中。
        '    Dispose(False)
        '    MyBase.Finalize()
        'End Sub

        ' Visual Basic 添加此代码是为了正确实现可处置模式。
        Public Sub Dispose() Implements IDisposable.Dispose
            ' 不要更改此代码。    请将清理代码放入上面的 Dispose (disposing As Boolean)中。
            Dispose(True)
            GC.SuppressFinalize(Me)
        End Sub
#End Region

    End Class
    Public Class SQLServer_CMD_Tran_item
        Public TBName As String = ""
        Public SQLStr As String = ""
        Public EffectCount As Integer = 0
        Public data As DataTable = Nothing
        Public Enum Enum_方式 As Integer
            Text = 0
            DataTable = 1
        End Enum
        Public 方式 As Enum_方式 = Enum_方式.Text
        Public Sub New(dt As DataTable, TBName As String)
            data = dt
            方式 = Enum_方式.DataTable
            Me.TBName = TBName
        End Sub
        Public Sub New(TBName As String, SQLStr As String)
            Me.TBName = TBName
            Me.SQLStr = SQLStr
        End Sub
        Public Sub New()
        End Sub
    End Class
#End Region
#End Region
End Class
